/*
Steps
1) Use excel to transpose to Base County Cty1 Cty2 Cty3 ....
2) Import to stata and reshape wide to long
3) Import to excel. 
		Clean adjacent names. 
		Make list of unique countynames and ID's and labels. 
		Use Vlookup to get baseID and adjID.
4) Import to stata. Copy/paste labels in do file. label values.
5) generate weighting matrix.
*/

clear all

local dirdata c:\Users\Mark\Desktop\
cd `dirdata'
dir *.csv



insheet using tx_county_neighbor2.csv, comma names
reshape long ct, i(base) j(Cty)
drop Cty 
drop if ct == ""

* encode base, gen(ctycode_base)
* label dir list

outsheet using tx_county_neighbor3.csv, comma names


insheet using tx_county_neighbor4.csv, comma names clear
* encode base_cty, gen(base_no)
* label values adj_cty


insheet using tx_county_neighbor5.csv, comma names clear

qui {
	label define cty 1 "Anderson", add
	label define cty 2 "Andrews", add
	label define cty 3 "Angelina", add
	label define cty 4 "Aransas", add
	label define cty 5 "Archer", add
	label define cty 6 "Armstrong", add
	label define cty 7 "Atascosa", add
	label define cty 8 "Austin", add
	label define cty 9 "Bailey", add
	label define cty 10 "Bandera", add
	label define cty 11 "Bastrop", add
	label define cty 12 "Baylor", add
	label define cty 13 "Bee", add
	label define cty 14 "Bell", add
	label define cty 15 "Bexar", add
	label define cty 16 "Blanco", add
	label define cty 17 "Borden", add
	label define cty 18 "Bosque", add
	label define cty 19 "Bowie", add
	label define cty 20 "Brazoria", add
	label define cty 21 "Brazos", add
	label define cty 22 "Brewster", add
	label define cty 23 "Briscoe", add
	label define cty 24 "Brooks", add
	label define cty 25 "Brown", add
	label define cty 26 "Burleson", add
	label define cty 27 "Burnet", add
	label define cty 28 "Caldwell", add
	label define cty 29 "Calhoun", add
	label define cty 30 "Callahan", add
	label define cty 31 "Cameron", add
	label define cty 32 "Camp", add
	label define cty 33 "Carson", add
	label define cty 34 "Cass", add
	label define cty 35 "Castro", add
	label define cty 36 "Chambers", add
	label define cty 37 "Cherokee", add
	label define cty 38 "Childress", add
	label define cty 39 "Clay", add
	label define cty 40 "Cochran", add
	label define cty 41 "Coke", add
	label define cty 42 "Coleman", add
	label define cty 43 "Collin", add
	label define cty 44 "Collingsworth", add
	label define cty 45 "Colorado", add
	label define cty 46 "Comal", add
	label define cty 47 "Comanche", add
	label define cty 48 "Concho", add
	label define cty 49 "Cooke", add
	label define cty 50 "Coryell", add
	label define cty 51 "Cottle", add
	label define cty 52 "Crane", add
	label define cty 53 "Crockett", add
	label define cty 54 "Crosby", add
	label define cty 55 "Culberson", add
	label define cty 56 "Dallam", add
	label define cty 57 "Dallas", add
	label define cty 58 "Dawson", add
	label define cty 59 "DeWitt", add
	label define cty 60 "Deaf Smith", add
	label define cty 61 "Delta", add
	label define cty 62 "Denton", add
	label define cty 63 "Dickens", add
	label define cty 64 "Dimmit", add
	label define cty 65 "Donley", add
	label define cty 66 "Duval", add
	label define cty 67 "Eastland", add
	label define cty 68 "Ector", add
	label define cty 69 "Edwards", add
	label define cty 70 "El Paso", add
	label define cty 71 "Ellis", add
	label define cty 72 "Erath", add
	label define cty 73 "Falls", add
	label define cty 74 "Fannin", add
	label define cty 75 "Fayette", add
	label define cty 76 "Fisher", add
	label define cty 77 "Floyd", add
	label define cty 78 "Foard", add
	label define cty 79 "Fort Bend", add
	label define cty 80 "Franklin", add
	label define cty 81 "Freestone", add
	label define cty 82 "Frio", add
	label define cty 83 "Gaines", add
	label define cty 84 "Galveston", add
	label define cty 85 "Garza", add
	label define cty 86 "Gillespie", add
	label define cty 87 "Glasscock", add
	label define cty 88 "Goliad", add
	label define cty 89 "Gonzales", add
	label define cty 90 "Gray", add
	label define cty 91 "Grayson", add
	label define cty 92 "Gregg", add
	label define cty 93 "Grimes", add
	label define cty 94 "Guadalupe", add
	label define cty 95 "Hale", add
	label define cty 96 "Hall", add
	label define cty 97 "Hamilton", add
	label define cty 98 "Hansford", add
	label define cty 99 "Hardeman", add
	label define cty 100 "Hardin", add
	label define cty 101 "Harris", add
	label define cty 102 "Harrison", add
	label define cty 103 "Hartley", add
	label define cty 104 "Haskell", add
	label define cty 105 "Hays", add
	label define cty 106 "Hemphill", add
	label define cty 107 "Henderson", add
	label define cty 108 "Hidalgo", add
	label define cty 109 "Hill", add
	label define cty 110 "Hockley", add
	label define cty 111 "Hood", add
	label define cty 112 "Hopkins", add
	label define cty 113 "Houston", add
	label define cty 114 "Howard", add
	label define cty 115 "Hudspeth", add
	label define cty 116 "Hunt", add
	label define cty 117 "Hutchinson", add
	label define cty 118 "Irion", add
	label define cty 119 "Jack", add
	label define cty 120 "Jackson", add
	label define cty 121 "Jasper", add
	label define cty 122 "Jeff Davis", add
	label define cty 123 "Jefferson", add
	label define cty 124 "Jim Hogg", add
	label define cty 125 "Jim Wells", add
	label define cty 126 "Johnson", add
	label define cty 127 "Jones", add
	label define cty 128 "Karnes", add
	label define cty 129 "Kaufman", add
	label define cty 130 "Kendall", add
	label define cty 131 "Kenedy", add
	label define cty 132 "Kent", add
	label define cty 133 "Kerr", add
	label define cty 134 "Kimble", add
	label define cty 135 "King", add
	label define cty 136 "Kinney", add
	label define cty 137 "Kleberg", add
	label define cty 138 "Knox", add
	label define cty 139 "La Salle", add
	label define cty 140 "Lamar", add
	label define cty 141 "Lamb", add
	label define cty 142 "Lampasas", add
	label define cty 143 "Lavaca", add
	label define cty 144 "Lee", add
	label define cty 145 "Leon", add
	label define cty 146 "Liberty", add
	label define cty 147 "Limestone", add
	label define cty 148 "Lipscomb", add
	label define cty 149 "Live Oak", add
	label define cty 150 "Llano", add
	label define cty 151 "Loving", add
	label define cty 152 "Lubbock", add
	label define cty 153 "Lynn", add
	label define cty 154 "Madison", add
	label define cty 155 "Marion", add
	label define cty 156 "Martin", add
	label define cty 157 "Mason", add
	label define cty 158 "Matagorda", add
	label define cty 159 "Maverick", add
	label define cty 160 "McCulloch", add
	label define cty 161 "McLennan", add
	label define cty 162 "McMullen", add
	label define cty 163 "Medina", add
	label define cty 164 "Menard", add
	label define cty 165 "Midland", add
	label define cty 166 "Milam", add
	label define cty 167 "Mills", add
	label define cty 168 "Mitchell", add
	label define cty 169 "Montague", add
	label define cty 170 "Montgomery", add
	label define cty 171 "Moore", add
	label define cty 172 "Morris", add
	label define cty 173 "Motley", add
	label define cty 174 "Nacogdoches", add
	label define cty 175 "Navarro", add
	label define cty 176 "Newton", add
	label define cty 177 "Nolan", add
	label define cty 178 "Nueces", add
	label define cty 179 "Ochiltree", add
	label define cty 180 "Oldham", add
	label define cty 181 "Orange", add
	label define cty 182 "Palo Pinto", add
	label define cty 183 "Panola", add
	label define cty 184 "Parker", add
	label define cty 185 "Parmer", add
	label define cty 186 "Pecos", add
	label define cty 187 "Polk", add
	label define cty 188 "Potter", add
	label define cty 189 "Presidio", add
	label define cty 190 "Rains", add
	label define cty 191 "Randall", add
	label define cty 192 "Reagan", add
	label define cty 193 "Real", add
	label define cty 194 "Red River", add
	label define cty 195 "Reeves", add
	label define cty 196 "Refugio", add
	label define cty 197 "Roberts", add
	label define cty 198 "Robertson", add
	label define cty 199 "Rockwall", add
	label define cty 200 "Runnels", add
	label define cty 201 "Rusk", add
	label define cty 202 "Sabine", add
	label define cty 203 "San Augustine", add
	label define cty 204 "San Jacinto", add
	label define cty 205 "San Patricio", add
	label define cty 206 "San Saba", add
	label define cty 207 "Schleicher", add
	label define cty 208 "Scurry", add
	label define cty 209 "Shackelford", add
	label define cty 210 "Shelby", add
	label define cty 211 "Sherman", add
	label define cty 212 "Smith", add
	label define cty 213 "Somervell", add
	label define cty 214 "Starr", add
	label define cty 215 "Stephens", add
	label define cty 216 "Sterling", add
	label define cty 217 "Stonewall", add
	label define cty 218 "Sutton", add
	label define cty 219 "Swisher", add
	label define cty 220 "Tarrant", add
	label define cty 221 "Taylor", add
	label define cty 222 "Terrell", add
	label define cty 223 "Terry", add
	label define cty 224 "Throckmorton", add
	label define cty 225 "Titus", add
	label define cty 226 "Tom Green", add
	label define cty 227 "Travis", add
	label define cty 228 "Trinity", add
	label define cty 229 "Tyler", add
	label define cty 230 "Upshur", add
	label define cty 231 "Upton", add
	label define cty 232 "Uvalde", add
	label define cty 233 "Val Verde", add
	label define cty 234 "Van Zandt", add
	label define cty 235 "Victoria", add
	label define cty 236 "Walker", add
	label define cty 237 "Waller", add
	label define cty 238 "Ward", add
	label define cty 239 "Washington", add
	label define cty 240 "Webb", add
	label define cty 241 "Wharton", add
	label define cty 242 "Wheeler", add
	label define cty 243 "Wichita", add
	label define cty 244 "Wilbarger", add
	label define cty 245 "Willacy", add
	label define cty 246 "Williamson", add
	label define cty 247 "Wilson", add
	label define cty 248 "Winkler", add
	label define cty 249 "Wise", add
	label define cty 250 "Wood", add
	label define cty 251 "Yoakum", add
	label define cty 252 "Young", add
	label define cty 253 "Zapata", add
	label define cty 254 "Zavala", add
}
label values baseid adjid cty
drop *name*

list in 1/50
list in 1/50, nolab
browse

mat define jnk = I(254)
forvalues n = 1/1336 {
	local i = baseid[`n']
	local j = adjid[`n']
	mat jnk[`i',`j'] = 2
}
mat list jnk


